package java0.homework.jdbc;

import java.sql.*;

/**
 * @author L.xf
 * @date 2021/10/22
 */
public class JdbcDemo {

    public static final String URL = "jdbc:mysql://47.107.148.237:3306/test_mysql_db?rewriteBatchedStatement=true";
    public static final String USER = "test_mysql_admin";
    public static final String PASSWORD = "test_mysql@2021";

    public static void main(String[] args) {
//        prepareDemo();
        simpleDemo();

    }


    /**
     * 使用 JDBC 原生接口，实现数据库的增删改查操作。
     */
    private static void simpleDemo() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            //3.操作数据库，实现增删改查
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, name FROM t_student");
            //如果有数据，rs.next()返回true
            while (rs.next()) {
                System.out.println(rs.getString("id") + " name：" + rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 使用事务，PrepareStatement 方式，批处理方式，改进上述操作。
     */
    private static void prepareDemo()  {
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            connection.setAutoCommit(false);
            //3.操作数据库，实现增删改查
            String sql = "insert into t_student(id,name)values(?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 10; i < 20; i++) {
                ps.setInt(1, i);
                ps.setString(2, "hello" + i);
                ps.addBatch();
            }
            connection.commit();
            ps.executeBatch();
            ps.close();
            connection.close();

        } catch (Exception e) {
            try {
                e.printStackTrace();
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}
